﻿Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Diagnostics
Imports System.Linq
Imports ATS.Business.Entities
Imports System.Collections.ObjectModel



Public Class AssetDataAccess

    Private Const ENTITY_SET_NAME_ASSETS As String = "MainAssetsTables"
    Private Const ENTITY_SET_NAME_USERS As String = "UserTables"
    Private Const ENTITY_SET_NAME_ACCOUNT_POLICY As String = "admPasswordConfigs"
    Private Const ENTITY_SET_NAME_ACCOUNT_COMPANYDETAIL As String = "CompanyDetails"
    Private Const ENTITY_SET_NAME_ACCOUNT_CATEGORY As String = "Categories"
    Private Const ENTITY_SET_NAME_ACCOUNT_LOCATION As String = "Locations"
    Private Const ENTITY_SET_NAME_ACCOUNT_PERSON As String = "Persons"
    Private Const ENTITY_SET_NAME_ACCOUNT_DEPARTMENT As String = "Departments"
    Private Const ENTITY_SET_NAME_ACCOUNT_DISPOSED_ASSET As String = "DisposedAssets"

#Region "Asset"
    Public Function CreateAsset(_AssetDetails As MainAssetsTable) As MainAssetsTable
        Using ctx As New AssetDataContext()
            Try
                ctx.MainAssetsTables.AddObject(_AssetDetails)
                ctx.SaveChanges()
            Catch ex As Exception

            End Try
            
        End Using
        Return _AssetDetails
    End Function
    Public Function GetAllAssets() As List(Of MainAssetsTable)
        Dim _AssetsList As List(Of MainAssetsTable) = Nothing
        Using ctx As New AssetDataContext
            _AssetsList = (From Asset In ctx.MainAssetsTables _
                                                      .Include("DisposedAsset") _
                                                      .Include("Category") _
                           ).ToList
        End Using
        Return _AssetsList
    End Function

    Public Function GetAsset(ByVal _AssetNumber As String) As MainAssetsTable
        Dim _Asset As MainAssetsTable = Nothing
        Using ctx As New AssetDataContext
            _Asset = (From Asset In ctx.MainAssetsTables Where Asset.Assetno.ToUpper = _AssetNumber.ToUpper).FirstOrDefault
        End Using
        Return _Asset
    End Function

    Public Function UpdateAssetDetails(ByVal _AssetDetails As MainAssetsTable) As MainAssetsTable
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            Try
                
                ctx.MainAssetsTables.ApplyChanges(_AssetDetails)
                'key = ctx.CreateEntityKey(ENTITY_SET_NAME_ASSETS, _AssetDetails)
                'If ctx.TryGetObjectByKey(key, original) Then

                '    ctx.ApplyCurrentValues(key.EntitySetName, _AssetDetails)
                'End If
                ctx.SaveChanges()
            Catch ex As Exception
                Dim z As String
                z = "ffff"
            End Try
        End Using
        Return _AssetDetails
    End Function

    Public Sub DeleteAsset(ByVal _AssetDetails As MainAssetsTable)
        Using ctx As New AssetDataContext()
            ctx.AttachTo("MainAssetsTables", _AssetDetails)
            ctx.DeleteObject(_AssetDetails)
            ctx.SaveChanges()
        End Using
    End Sub
#End Region

#Region "UserTable Data Access"
    Public Function CreateUser(userDetails As UserTable) As UserTable
        Using ctx As New AssetDataContext()
            ctx.UserTables.AddObject(userDetails)
            ctx.SaveChanges()
        End Using
        Return userDetails
    End Function
    Public Function GetAllUsers() As List(Of UserTable)
        Dim _userTable As List(Of UserTable) = Nothing
        Using ctx As New AssetDataContext
            _userTable = (From user In ctx.UserTables).ToList
        End Using
        Return _userTable
    End Function

    Public Function GetUserDetails(ByVal username As String) As UserTable
        Dim _userTable As UserTable = Nothing
        Using ctx As New AssetDataContext
            _userTable = (From user In ctx.UserTables Where user.Username.ToUpper = username.ToUpper).FirstOrDefault
        End Using
        Return _userTable
    End Function

    Public Sub UpdateUserTable(ByVal user As UserTable)
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_USERS, user)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, user)
            End If
            ctx.SaveChanges()
        End Using
    End Sub

    Public Sub DeleteUser(ByVal userdetail As UserTable)
        Using ctx As New AssetDataContext()
            ctx.AttachTo("UserTables", userdetail)
            ctx.DeleteObject(userdetail)
            ctx.SaveChanges()
        End Using
    End Sub

#End Region

#Region "Company Details"
    Public Function CreateCompanyDetail(companyDetail As CompanyDetail) As CompanyDetail
        Using ctx As New AssetDataContext()
            ctx.CompanyDetails.AddObject(companyDetail)
            ctx.SaveChanges()
        End Using
        Return companyDetail
    End Function

    Public Function ReadCompanyDetails() As CompanyDetail
        Dim _companyDetail As CompanyDetail = Nothing
        Using ctx As New AssetDataContext
            _companyDetail = (From companyDetail In ctx.CompanyDetails).FirstOrDefault
        End Using
        Return _companyDetail
    End Function

    Public Sub UpdateCompanyDetails(ByVal companyDetail As CompanyDetail)
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_ACCOUNT_COMPANYDETAIL, companyDetail)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, companyDetail)
            End If
            ctx.SaveChanges()
        End Using
    End Sub
#End Region
    
#Region "Password Config Data Access"
    Public Function GetPasswordConfigDetails() As admPasswordConfig
        Dim _passwordConfigTable As admPasswordConfig = Nothing
        Using ctx As New AssetDataContext
            _passwordConfigTable = (From passConfig In ctx.admPasswordConfigs).FirstOrDefault
        End Using
        Return _passwordConfigTable
    End Function

    Public Function CreatePasswordConfigDetails(accountPolicyValues As admPasswordConfig) As admPasswordConfig
        Using ctx As New AssetDataContext()
            ctx.admPasswordConfigs.AddObject(accountPolicyValues)
            ctx.SaveChanges()
        End Using
        Return accountPolicyValues
    End Function

    Public Sub UpdatePasswordConfigDetails(accountPolicyValues As admPasswordConfig)
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_ACCOUNT_POLICY, accountPolicyValues)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, accountPolicyValues)
            End If
            ctx.SaveChanges()
        End Using
    End Sub

#End Region

#Region "RFID Scan History"
    Public Sub CreateRFIDScanHistory(_RFIDScanHistory As RFIDScanHistory)
        Using ctx As New AssetDataContext()
            ctx.RFIDScanHistories.AddObject(_RFIDScanHistory)
            ctx.SaveChanges()
        End Using
    End Sub

    Public Function ReadRFIDScanHistoryByRFIDNumber(_RFIDNumber As String, Optional _RFIDScanDate As DateTime = #1/1/1900#) As List(Of RFIDScanHistory)

        Dim _RFIDScanHistory As List(Of RFIDScanHistory) = Nothing
        Using ctx As New AssetDataContext
            _RFIDScanHistory = (From RFIDSH In ctx.RFIDScanHistories Where _
                                RFIDSH.RFIDNum.ToUpper = _RFIDNumber.ToUpper _
                                And If(_RFIDScanDate = #1/1/1900# _
                                       , RFIDSH.DateScanned = RFIDSH.DateScanned _
                                       , RFIDSH.DateScanned.Year = _RFIDScanDate.Year And _
                                       RFIDSH.DateScanned.Month = _RFIDScanDate.Month And _
                                       RFIDSH.DateScanned.Day = _RFIDScanDate.Day)).ToList
        End Using
        Return _RFIDScanHistory
    End Function

    Public Function ReadRFIDScanHistoryByScanDate(_scanDateFrom As Date, _scanDateTo As Date) As List(Of RFIDScanHistory)
        Dim _RFIDScanHistory As List(Of RFIDScanHistory) = Nothing
        Using ctx As New AssetDataContext
            _RFIDScanHistory = (From RFIDSH In ctx.RFIDScanHistories Where
                                RFIDSH.DateScanned >= _scanDateFrom And
                                RFIDSH.DateScanned <= _scanDateTo).ToList
        End Using
        Return _RFIDScanHistory
    End Function



#End Region

#Region "Category"
    Public Function CreateCategory(categoryDetails As Category) As Category
        Using ctx As New AssetDataContext()
            ctx.Categories.AddObject(categoryDetails)
            ctx.SaveChanges()
        End Using
        Return categoryDetails
    End Function
    Public Function ReadAllCategories() As List(Of Category)
        Dim _categories As List(Of Category) = Nothing
        Using ctx As New AssetDataContext
            _categories = (From category In ctx.Categories).ToList
        End Using
        Return _categories
    End Function

    Public Function ReadCategory(ByVal categoryCode As String) As Category
        Dim _Category As Category = Nothing
        Using ctx As New AssetDataContext
            _Category = (From Category In ctx.Categories Where Category.CategoryCode.ToUpper = categoryCode.ToUpper).FirstOrDefault
        End Using
        Return _Category
    End Function

    Public Function GetAllCategoryClass() As List(Of String)
        Dim _classList As List(Of String) = Nothing
        Using ctx As New AssetDataContext
            _classList = (From category In ctx.Categories
                         Group category By category.AssetClass Into Group
                         Select AssetClass).ToList
            Return _classList
        End Using
    End Function


    Public Function UpdateCategory(ByVal _Category As Category) As Category
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_ACCOUNT_CATEGORY, _Category)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, _Category)
            End If
            ctx.SaveChanges()
        End Using

        Return _Category
    End Function

    Public Sub DeleteCategory(ByVal _Category As Category)
        Using ctx As New AssetDataContext()
            ctx.AttachTo("Categories", _Category)
            ctx.DeleteObject(_Category)
            ctx.SaveChanges()
        End Using
    End Sub
#End Region

#Region "Location"
    Public Function CreateLocation(locationDetails As Location) As Location
        Using ctx As New AssetDataContext()
            ctx.Locations.AddObject(locationDetails)
            ctx.SaveChanges()
        End Using
        Return locationDetails
    End Function
    Public Function ReadAllLocations() As List(Of Location)
        Dim _locations As List(Of Location) = Nothing
        Using ctx As New AssetDataContext
            _locations = (From location In ctx.Locations).ToList
        End Using
        Return _locations
    End Function

    Public Function ReadLocation(ByVal locationCode As String) As Location
        Dim _location As Location = Nothing
        Using ctx As New AssetDataContext
            _location = (From location In ctx.Locations Where location.LocationCode.ToUpper = locationCode.ToUpper).FirstOrDefault
        End Using
        Return _location
    End Function

    Public Function UpdateLocation(ByVal _location As Location) As Location
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_ACCOUNT_LOCATION, _location)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, _location)
            End If
            ctx.SaveChanges()
        End Using

        Return _location
    End Function

    Public Sub DeleteLocation(ByVal _location As Location)
        Using ctx As New AssetDataContext()
            ctx.AttachTo("Locations", _location)
            ctx.DeleteObject(_location)
            ctx.SaveChanges()
        End Using
    End Sub
#End Region

#Region "Person"
    Public Function CreatePerson(_personDetails As Person) As Person
        Using ctx As New AssetDataContext()
            ctx.Persons.AddObject(_personDetails)
            ctx.SaveChanges()
        End Using
        Return _personDetails
    End Function
    Public Function GetAllPersons() As List(Of Person)
        Dim _personsList As List(Of Person) = Nothing
        Using ctx As New AssetDataContext
            _personsList = (From person In ctx.Persons).ToList
        End Using
        Return _personsList
    End Function

    Public Function GetPerson(ByVal _searchString As String) As Person
        Dim _person As Person = Nothing
        Using ctx As New AssetDataContext
            _person = (From person In ctx.Persons Where person.EmployeeID.ToUpper = _searchString.ToUpper).FirstOrDefault
        End Using
        Return _person
    End Function

    Public Function UpdatePersonDetails(ByVal _personDetails As Person) As Person
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_ACCOUNT_PERSON, _personDetails)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, _personDetails)
            End If
            ctx.SaveChanges()
        End Using

        Return _personDetails
    End Function

    Public Sub DeletePerson(ByVal _personDetails As Person)
        Using ctx As New AssetDataContext()
            ctx.AttachTo("Persons", _personDetails)
            ctx.DeleteObject(_personDetails)
            ctx.SaveChanges()
        End Using
    End Sub
#End Region

#Region "Department"
    Public Function CreateDepartment(_departmentDetails As Department) As Department
        Using ctx As New AssetDataContext()
            ctx.departments.AddObject(_departmentDetails)
            ctx.SaveChanges()
        End Using
        Return _departmentDetails
    End Function
    Public Function GetAllDepartments() As List(Of Department)
        Dim _departmentsList As List(Of Department) = Nothing
        Using ctx As New AssetDataContext
            _departmentsList = (From department In ctx.Departments).ToList
        End Using
        Return _departmentsList
    End Function
    Public Function GetDepartmentList() As List(Of String)
        Dim _departmentsList As List(Of String) = Nothing
        Using ctx As New AssetDataContext
            _departmentsList = (From department In ctx.Departments _
                                Group department By department.DepartmentName Into Group _
                                Select DepartmentName Order By DepartmentName).ToList
        End Using
        Return _departmentsList
    End Function

    Public Function GetDepartment(ByVal _departmentName As String) As Department
        Dim _department As Department = Nothing
        Using ctx As New AssetDataContext
            _department = (From department In ctx.Departments Where department.DepartmentCode.ToUpper = _departmentName.ToUpper).FirstOrDefault
        End Using
        Return _department
    End Function

    Public Function UpdateDepartmentDetails(ByVal _departmentDetails As Department) As Department
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_ACCOUNT_department, _departmentDetails)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, _departmentDetails)
            End If
            ctx.SaveChanges()
        End Using

        Return _departmentDetails
    End Function

    Public Sub Deletedepartment(ByVal _departmentDetails As Department)
        Using ctx As New AssetDataContext()
            ctx.AttachTo("Departments", _departmentDetails)
            ctx.DeleteObject(_departmentDetails)
            ctx.SaveChanges()
        End Using
    End Sub
#End Region

#Region "DisposedAsset"
    Public Function CreateDisposedAsset(_DisposedAssetDetails As DisposedAsset) As DisposedAsset
        Using ctx1 As New AssetDataContext()
            Try
                ctx1.DisposedAssets.AddObject(_DisposedAssetDetails)
                ctx1.SaveChanges()
            Catch ex As Exception
                Dim az As String

                az = "ff"


            End Try

        End Using
        Return _DisposedAssetDetails
    End Function
    Public Function GetAllDisposedAssets() As List(Of DisposedAsset)
        Dim _DisposedAssetsList As List(Of DisposedAsset) = Nothing
        Using ctx As New AssetDataContext
            _DisposedAssetsList = (From DisposedAsset In ctx.DisposedAssets).ToList
        End Using
        Return _DisposedAssetsList
    End Function
    Public Function GetDisposalMethods() As List(Of String)
        Dim _DisposedAssetsList As List(Of String) = Nothing
        Using ctx As New AssetDataContext
            _DisposedAssetsList = (From DAsset In ctx.DisposedAssets Select DAsset.DisposalMethod Order By DisposalMethod).ToList
        End Using
        Return _DisposedAssetsList
    End Function

    Public Function GetDisposedAsset(ByVal _DisposedAssetNumber As String) As DisposedAsset
        Dim _DisposedAsset As DisposedAsset = Nothing
        Using ctx As New AssetDataContext
            _DisposedAsset = (From DAsset In ctx.DisposedAssets Where DAsset.Assetno.ToUpper = _DisposedAssetNumber.ToUpper).FirstOrDefault
        End Using
        Return _DisposedAsset
    End Function

    Public Function UpdateDisposedAssetDetails(ByVal _DisposedAssetDetails As DisposedAsset) As DisposedAsset
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_ACCOUNT_DISPOSED_ASSET, _DisposedAssetDetails)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, _DisposedAssetDetails)
            End If
            ctx.SaveChanges()
        End Using

        Return _DisposedAssetDetails
    End Function

    Public Sub DeleteDisposedAsset(ByVal _DisposedAssetDetails As DisposedAsset)
        Using ctx As New AssetDataContext()
            ctx.AttachTo("DisposedAssets", _DisposedAssetDetails)
            ctx.DeleteObject(_DisposedAssetDetails)
            ctx.SaveChanges()
        End Using
    End Sub
#End Region

#Region "original content"
    ''' <summary>
    ''' Inserts an expense row.
    ''' </summary>
    ''' <param name="expense">An Expense object.</param>
    Public Function Create(ByVal asset As MainAssetsTable) As MainAssetsTable
        Using ctx As New AssetDataContext()
            ctx.MainAssetsTables.AddObject(asset)
            ctx.SaveChanges()
        End Using
        Return asset
    End Function
    ''' <summary>
    ''' Updates an Expense row.
    ''' </summary>
    ''' <param name="expense">A Expense object.</param>
    Public Sub Update(ByVal asset As MainAssetsTable)
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New AssetDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_ASSETS, asset)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, asset)
            End If
            ctx.SaveChanges()
        End Using
    End Sub
    Public Sub Delete(ByVal asset As MainAssetsTable)
        Using ctx As New AssetDataContext()
            ctx.DeleteObject(asset)
            ctx.SaveChanges()
        End Using
    End Sub
    ' ''' <summary>
    ' ''' Returns a set of Expenses that belongs to an employee
    ' ''' </summary>
    ' ''' <param name="employeeID">An EmployeeID.</param>
    ' ''' <returns>A List of expenses.</returns>
    'Public Function SelectForEmployee(ByVal employee As String) As List(Of Expense)
    '    Dim resultsList As List(Of Expense) = Nothing

    '    Using ctx As New ExpenseDataContext()
    'resultsList = (From expense In ctx.Expenses Where expense.Employee = employeeOrder By expense.DateSubmitted Descendingexpense).ToList()
    '    End Using

    '    Return resultsList

    'End Function

    'Public Function SelectForApproval(ByVal reviewer As String) As List(Of Expense)
    '    Dim resultsList As List(Of Expense) = Nothing

    '    Using ctx As New ExpenseDataContext()
    'resultsList = (From expense In ctx.Expenses Where expense.AssignedTo = reviewer AndAlso expense.IsCompleted = FalseOrder By expense.DateSubmitted Descendingexpense).ToList()
    '    End Using

    '    Return resultsList

    'End Function
    Public Function SelectAsset(Optional AssetNumber As String = "", Optional RFIDNumber As String = "") As MainAssetsTable
        Dim resultsList As MainAssetsTable = Nothing

        Using ctx As New AssetDataContext()
            resultsList = (From asset In ctx.MainAssetsTables
                           Where asset.Assetno.Contains(AssetNumber) _
                           Or asset.RFIDno.Contains(RFIDNumber) _
                           Order By asset.Assetno).FirstOrDefault
        End Using

        Return resultsList

    End Function

    Public Sub Purge()
        Using ctx As New AssetDataContext()
            Dim resultList As List(Of MainAssetsTable) = (From asset In ctx.MainAssetsTables Select asset).ToList()

            For Each asset As MainAssetsTable In resultList
                ctx.DeleteObject(asset)
            Next
            ctx.SaveChanges()
        End Using
    End Sub
#End Region
End Class

